package DBLayer;

import java.util.ArrayList;
import ModelLayer.*;

import java.sql.*;

public class DBPartOrder implements IFDBPartOrder {
	private Connection con = DBConnection.getInstance().getDBcon();
	private DBProduct dbPro;
	private DBSalesOrder dbSo;
	
	public DBPartOrder()
	{
		con = DBConnection.getInstance().getDBcon();
		
		dbPro = new DBProduct();
	}

	@Override
	public ArrayList<PartOrder> getAllPartOrders(int orderId)
	{
		String wClause = " WHERE salesOrderId = '" + orderId + "'";		
		return miscWhere(wClause);
	}

	@Override
	public PartOrder searchPartOrder(int poId) {
		
		String wClause = " partOrderId = '" + poId + "'";
		return singleWhere(wClause);
	}

	@Override
	public int insertPartOrder(PartOrder po) throws Exception {
		//call to get the next id number
        int nextID = GetMax.getMaxId("Select max(partOrderId) from partOrder");
        nextID = nextID + 1;
        System.out.println("next id = " +  nextID);
		int rc = -1;
		String query="INSERT INTO PartOrder(partOrderId, partPrice, amount, productId, salesOrderId)  VALUES('"+
				nextID  + "','"  +
				po.getPartPrice()  + "','"  +
				po.getAmount() + "','" +
				po.getP().getProductId() + "','" +
				po.getSo().getOrderId() + "')";


		System.out.println("insert : " + query);
		try{ // insert new supplier
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(SQLException ex){
			System.out.println("PartOrder not created");
			throw new Exception ("PartOrder is not inserted correct");
		}
		return(rc);
	}

	@Override
	public int updatePartOrder(PartOrder po) {
		// This is currently not being used.
		PartOrder poObj  = po;
		int rc=-1;

		String query="UPDATE partOrder SET "+
				"partOrderId ='"+ poObj.getPartOrderId()+"', "+
				"partPrice ='"+ poObj.getPartPrice() + "', " +
				"amount ='"+ poObj.getAmount() + "', " +
				"productId ='"+ poObj.getP().getProductId() + "' " +
				"salesOrderId ='"+ poObj.getSo().getOrderId() + "' " +
				" WHERE productId = '"+ poObj.getPartOrderId() + "'";
		System.out.println("Update query:" + query);
		try{ // update employee
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}//end try
		catch(Exception ex){
			System.out.println("Update exception in employee db: "+ex);
		}
		return(rc);
	}

	@Override
	public int deletePartOrder(PartOrder po) {
		int rc=-1;

		String query="DELETE FROM partOrder WHERE partOrderId = '" +
				po.getPartOrderId() + "'";
		System.out.println(query);
		try{ // delete from supplier
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex){
			System.out.println("Delete exception in PartOrder db: "+ex);
		}
		return(rc);
	}
	
	private ArrayList<PartOrder> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<PartOrder> list = new ArrayList<PartOrder>();	

		String query =  buildQuery(wClause);

		try{ // read the supplier from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while( results.next() ){
				PartOrder poObj = new PartOrder();
				poObj = buildPartOrder(results);	
				list.add(poObj);	
			}//end while
			stmt.close();  

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	private PartOrder singleWhere(String wClause)
	{
		ResultSet results;
		PartOrder poObj = new PartOrder();

		String query =  buildQuery(wClause);
		System.out.println(query);
		try{ // read the product from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() ){
				poObj = buildPartOrder(results);
				stmt.close();
			}
			else{ //no product was found
				poObj = null;
			}
		}//end try	
		catch(Exception e){
			System.out.println("Query exception: "+e);
		}
		return poObj;
	}
	
	private String buildQuery(String wClause)
	{
		String query="SELECT partOrderId, partPrice, amount, productId, salesOrderId  FROM PartOrder";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	private PartOrder buildPartOrder(ResultSet results)
	{   PartOrder poObj = new PartOrder();
	try{ // the columns from the table supplier are used
		poObj.setPartOrderId(results.getInt("partOrderId"));
		poObj.setPartPrice(results.getDouble("partPrice"));
		poObj.setAmount(results.getInt("amount"));		
		poObj.setP(dbPro.searchProduct(results.getInt("productId")));
		poObj.setSo(dbSo.searchSalesOrder(results.getInt("salesOrderId")));
		
	}
	catch(Exception e)
	{
		System.out.println("error in building the PartOrder object");
	}
	return poObj;
	}
}
	
	
